
###########################################################################################
####### Estimate SLES + Create Analysis Data Files for "Legislative Effectiveness in the American States"
##########################################################################################
### Includes code to estimate our State Legislative Effectivness Scores and...
### prepare the files used in the analysis starting from the pre-coded legislative bill files
###########################################################################################
### Originally run using R version 4.0.2 (2020-06-22)
###########################################################################################
### *** Note that the aggregated bill file used in this script is fairly large (~1.8M bills) 
### *** If your machine cannot load all the data at one time, you can skip this script and 
### *** use the pre-created output tables that are already in the replication file. 
###########################################################################################

#rm(list=ls())
options(stringsAsFactors = FALSE, scipen = 999)

### **********************************************************************
### Set Working Directory to Folder Containing Replication Files and Data
### **********************************************************************
working_directory <- ""

setwd(working_directory)

### Install/Load Packages
# install.packages(c("tidyverse"))
library(tidyverse) # version 1.3.0


#######################################################
## Load Covariate Files
####################################################

legislator_covars <- read_csv("Data/covariates_all_legislators.csv", col_types = cols())
glimpse(legislator_covars)

state_covars <- read_csv("Data/covariates_all_states.csv", col_types = cols())
glimpse(state_covars)

######################################################################################
## Estimation Prep: Create Counts of Sponsored Bills x Progress for each Legislator x Chamber x Term
#####################################################################################

### Path to the bill file for each state
### -- 1 file per state, each with 1 row per bill
## -- *** Note that these do NOT include all bills introduced during each session
## -- *** For example, resolutions are omitted. See the appendix for details on our data collection and cleaning process. 
all_bill_files <- list.files("Data/bills_by_state", full.names = TRUE)

### Load all bill files
## -- Note: title/keyword/url columns are missing for some states, depending on the format of the underlying raw data. 
all_bills <- all_bill_files %>%
  map_df(~read_csv(., col_types = cols(.default = 'c', klarner_id = 'd', introduced = 'd', action_in_comm = 'd', 
                                       action_beyond_comm = 'd', passed_chamber = 'd', law = 'd', SS = 'd', commem = 'd')))

glimpse(all_bills)

### Aggregate to 15 SLES Component Parts (1 row per legislator x term)
sles_components <- all_bills %>%
  group_by(state, term, chamber, sles_sponsor) %>%
  summarize(
    ### Overall Legislator x Chamber x Term Totals
    all_bills = sum(introduced), # Bill was introduced -- 0 if pulled before action
    all_aic = sum(action_in_comm), # Bill received action in committe
    all_abc = sum(action_beyond_comm), # Bill received action beyond committee
    all_pass = sum(passed_chamber), # Bill passed chamber where introduced
    all_law = sum(law), # Bill became law
    ### Substantive & Significant Only
    ss_bills = sum(SS * introduced),
    ss_aic = sum(SS * action_in_comm),
    ss_abc = sum(SS * action_beyond_comm),
    ss_pass = sum(SS * passed_chamber),
    ss_law = sum(SS * law),
    ### Substantive Only
    s_bills = sum(ifelse(SS == 0 & commem == 0, introduced, 0)),
    s_aic = sum(ifelse(SS == 0 & commem == 0, action_in_comm, 0)),
    s_abc = sum(ifelse(SS == 0 & commem == 0, action_beyond_comm, 0)),
    s_pass = sum(ifelse(SS == 0 & commem == 0, passed_chamber, 0)),
    s_law = sum(ifelse(SS == 0 & commem == 0, law, 0)),
    ### Commemorative Only
    c_bills = sum(commem * introduced),
    c_aic = sum(commem * action_in_comm),
    c_abc = sum(commem * action_beyond_comm),
    c_pass = sum(commem * passed_chamber),
    c_law = sum(commem * law),
  ) %>%
  ungroup()

glimpse(sles_components)

rm(all_bills)

######################################################################################
## Estimate the SLES 
#####################################################################################

sles <- legislator_covars %>%
  left_join(sles_components, by = c("state", "term", "chamber", "sles_sponsor")) %>% 
  ## Legislator covariate file includes legislators with 0 sponsored bills (who are thus missing from the bill files)
  replace_na(list(all_bills = 0, all_aic = 0, all_abc = 0, all_pass = 0, all_law = 0,
                  ss_bills = 0, ss_aic = 0, ss_abc = 0, ss_pass = 0, ss_law = 0,
                  s_bills = 0, s_aic = 0, s_abc = 0, s_pass = 0, s_law = 0,
                  c_bills = 0, c_aic = 0, c_abc = 0, c_pass = 0, c_law = 0)) %>%
  ## Estimate Scores by State-Chamber-Term
  group_by(state, chamber, term) %>%
  mutate(
    BILL_wshare = (1 * c_bills + 5 * s_bills + 10 * ss_bills) / (1 * sum(c_bills) + 5 * sum(s_bills) + 10 * sum(ss_bills)),
    AIC_wshare = (1 * c_aic + 5 * s_aic + 10 * ss_aic) / (1 * sum(c_aic) + 5 * sum(s_aic) + 10 * sum(ss_aic)),
    ABC_wshare = (1 * c_abc + 5 * s_abc + 10 * ss_abc) / (1 * sum(c_abc) + 5 * sum(s_abc) + 10 * sum(ss_abc)),
    PASS_wshare = (1 * c_pass + 5 * s_pass + 10 * ss_pass) / (1 * sum(c_pass) + 5 * sum(s_pass) + 10 * sum(ss_pass)),
    LAW_wshare = (1 * c_law + 5 * s_law + 10 * ss_law) / (1 * sum(c_law) + 5 * sum(s_law) + 10 * sum(ss_law)),
    SLES = (BILL_wshare + AIC_wshare + ABC_wshare + PASS_wshare + LAW_wshare) * n() / 5
  ) %>%
  ungroup() %>%
  ### Create SLES Rank Variable
  group_by(state, chamber, term) %>% 
  arrange(desc(SLES)) %>% 
  mutate(SLES_rank = 1:n()) %>% 
  ungroup() %>% 
  ### Create Normalized Version of SLES + Passage/Law Rate Vars
  mutate(
    SLES_z = (SLES - mean(SLES)) / sd(SLES),
    sponsor_pass_rate = ifelse(all_bills > 0, all_pass / all_bills, 0),
    sponsor_law_rate = ifelse(all_bills > 0, all_law / all_bills, 0),
  ) %>%
  ### Organize
  select(state, chamber, term, sles_sponsor, sles_id, year_elected, district, party, in_majority,
         SLES, SLES_rank, SLES_z,
         BILL_wshare, AIC_wshare, ABC_wshare, PASS_wshare, LAW_wshare,
         all_bills, all_aic, all_abc, all_pass, all_law,
         ss_bills, ss_aic, ss_abc, ss_pass, ss_law,
         s_bills, s_aic, s_abc, s_pass, s_law,
         c_bills, c_aic, c_abc, c_pass, c_law,
         term_start, term_end, term_elec_yr, term_length, biennial_grps, state_chamber, chamber_size,
         sponsor_pass_rate, sponsor_law_rate, 
         female, pred.race, seniority, seniority_elec,
         first_elected, last_elected, terms_served, terms_served_lower, terms_served_upper,
         klarner_name_adj, klarner_name, klarner_id,
         vote_share, appt_or_won_special,
         np_score, ideo_med_distance,
         Leader, Leader_speakerpres, Leader_majleader, Leader_minleader,
         comm_chair, chair_of, power_comm, 
         aj_comm_gatekeep, aj_deny_hearing, aj_deny_report,
         aj_anymaj_setscal, aj_majleader_setscal, aj_majcom_setscal,
         squire_year, squire_index, leg_salary_yr_avg, slength_avg_all, ncsl_num_staff_per_leg)

### Check Overall SLES Means Equal 1
mean(sles$SLES)

### Check SLES Mean Equals 1 within State-Chamber-Terms
sles %>% 
  group_by(state, chamber, term) %>% 
  summarize(mean_SLES = mean(SLES)) %>% 
  ungroup() %>% 
  summarize(max_SLES_mean = max(mean_SLES),
            min_SLES_mean = min(mean_SLES))

rm(sles_components)

######################################################################################
## Create State-Chamber-Term Level SLES Aggregate Metrics
#####################################################################################

sles_state_agg <- sles %>%
  group_by(state, chamber, term) %>%
  summarize(
    ### Majority/Minority Counts
    n_maj = sum(in_majority == 1),
    n_min = sum(in_majority == 0),
    ### SLES Majority/Minority Medians + Median Difference
    maj_SLES_median = median(SLES[in_majority == 1], na.rm = TRUE),
    min_SLES_median = median(SLES[in_majority == 0], na.rm = TRUE),
    maj_min_SLES_meddiff = maj_SLES_median - min_SLES_median,
    ### Share of Majority Members with SLES Above the Minority Median
    maj_above_min_SLES_median = ifelse(n_min > 0, sum(SLES[in_majority == 1] > min_SLES_median, na.rm = T) / n_maj, 1),
    ### Chair SLES Medians + Difference
    chair_SLES_median = median(SLES[comm_chair == 1], na.rm = TRUE),
    notchair_SLES_median = median(SLES[comm_chair == 0], na.rm = TRUE),
    chair_SLES_meddiff = chair_SLES_median - notchair_SLES_median,
    ### Majority: Seniority Median Difference
    majfrosh_SLES_meddiff = median(SLES[seniority > 1 & in_majority == 1], na.rm = TRUE) - median(SLES[seniority == 1 & in_majority == 1], na.rm = TRUE),
    ### Minority: Seniority Median Difference
    minfrosh_SLES_meddiff = median(SLES[seniority > 1 & in_majority == 0], na.rm = TRUE) - median(SLES[seniority == 1 & in_majority == 0], na.rm = TRUE)
  ) %>%
  ungroup() %>%
  ### Join in State-Level Covariates
  left_join(state_covars %>% select(-n_maj, -n_min), 
            by = c("state", "chamber", "term")) %>%
  ### Organize
  select(state, chamber, state_chamber, 
         term, term_start, term_end, term_elec_yr, term_length, 
         n_maj, n_min, 
         chamber_size, legislature_size, num_chamber_comms, num_joint_comms, 
         maj_SLES_median, 
         min_SLES_median, 
         maj_min_SLES_meddiff, 
         maj_above_min_SLES_median,
         chair_SLES_median, 
         notchair_SLES_median, 
         chair_SLES_meddiff, 
         majfrosh_SLES_meddiff,
         minfrosh_SLES_meddiff,
         num_female, share_female, 
         ### KPB = Klarner Partisan Balance Data
         ### share_dem = observed % democrat (the two differ primarily based on appointments and special elections)
         kpb_dem_share, kpb_rep_share, share_dem, partisan_seatshare_diff,
         term_limits_enacted, term_limits_applied,
         starts_with("aj_"),
         SM_maj_median, SM_min_median, SM_median_diff, SM_maj_sd, SM_min_sd,
         squire_year, squire_index, 
         leg_salary_yr_avg, slength_avg_all, 
         ncsl_staff_year, ncsl_num_staff, ncsl_num_staff_per_leg,
         maj_party_controls_gov, maj_party_controls_oppo_chamber, unified_gov) 

glimpse(sles_state_agg)



######################################################################################
## Save Individual and State-Level Files to Use in the Analysis
#####################################################################################

### Individual Level
write.csv(sles, "Data/SLES_individual_level.csv", row.names = FALSE)


### State Level
write.csv(sles_state_agg, "Data/SLES_state_level.csv", row.names = FALSE)



